package dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;

import com.mysql.jdbc.PreparedStatement;

import dto.UserDto;

public class UserDao {

	public String insertUser(UserDto user) {

		if (user == null)
			return "Insert failed! Input data is null.";
		String response = "";

		Connection connection = DataProvider.getConnection();
		String sql = "INSERT INTO USER( name, email_address, dob, role) VALUES (?,?,?,?)";

		PreparedStatement preparedStatement;
		try {
			preparedStatement = (PreparedStatement) connection
					.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			// preparedStatement.setString(1, user.getUserId());
			preparedStatement.setString(1, user.getName());
			preparedStatement.setString(2, user.getEmailAddress());
			Date date = user.getDob();
			preparedStatement.setDate(3, new java.sql.Date(date.getTime()));
			preparedStatement.setString(4, user.getRole());

			preparedStatement.execute();
			ResultSet rs = preparedStatement.getGeneratedKeys();
			if (rs.next()) {
				user.setUserId(rs.getInt(1));
			}
			response = "Inserted successfully!";

			preparedStatement.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
			response = "Insert failed! " + e.getMessage();
		}

		return response;
	}

	public String deleteUser(String userId) {

		String response = "";

		Connection connection = DataProvider.getConnection();
		String sql = "DELETE FROM USER WHERE user_id = ?";

		PreparedStatement preparedStatement;
		try {
			preparedStatement = (PreparedStatement) connection
					.prepareStatement(sql);
			preparedStatement.setString(1, userId);

			preparedStatement.execute();
			response = "Deleted successfully!";

			preparedStatement.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
			response = "Delete failed! " + e.getMessage();
		}

		return response;
	}

	public String updateUser(UserDto user) {

		String response = "";

		Connection connection = DataProvider.getConnection();
		String sql = "UPDATE USER SET name = ?, email_address = ?, dob = ?, role = ? WHERE user_id = ?";

		PreparedStatement preparedStatement;
		try {
			preparedStatement = (PreparedStatement) connection
					.prepareStatement(sql);

			preparedStatement.setString(1, user.getName());
			preparedStatement.setString(2, user.getEmailAddress());
			Date date = user.getDob();
			preparedStatement.setDate(3, new java.sql.Date(date.getTime()));
			preparedStatement.setString(4, user.getRole());
			preparedStatement.setInt(5, user.getUserId());

			preparedStatement.execute();
			response = "Updated successfully!";

			preparedStatement.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
			response = "Update failed! " + e.getMessage();
		}

		return response;
	}

	public UserDto getUserById(String userId) {
		UserDto user = null;

		Connection connection = DataProvider.getConnection();
		String sql = "SELECT * FROM USER WHERE user_id = ?";

		PreparedStatement preparedStatement;
		try {
			preparedStatement = (PreparedStatement) connection
					.prepareStatement(sql);
			preparedStatement.setString(1, userId);
			ResultSet resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				user = new UserDto();
				user.setUserId(resultSet.getInt("user_id"));
				user.setName(resultSet.getString("name"));
				user.setEmailAddress(resultSet.getString("email_address"));
				try {
					user.setDob(resultSet.getTimestamp("dob"));
				} catch (Exception e) {
					user.setRole(null);
				}
				user.setRole(resultSet.getString("role"));
			}

			preparedStatement.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return user;
	}

	public UserDto getUserByDetail(UserDto user) {

		Connection connection = DataProvider.getConnection();
		String sql = "SELECT user_id FROM USER WHERE name = ? and email_address = ? and dob = ? and role = ?";

		PreparedStatement preparedStatement;
		try {
			preparedStatement = (PreparedStatement) connection
					.prepareStatement(sql);
			preparedStatement.setString(1, user.getName());
			preparedStatement.setString(2, user.getEmailAddress());
			preparedStatement.setDate(3, new java.sql.Date(user.getDob()
					.getTime()));
			preparedStatement.setString(4, user.getRole());

			ResultSet resultSet = preparedStatement.executeQuery();
			while (resultSet.next()) {
				user.setUserId(resultSet.getInt("user_id"));
			}

			preparedStatement.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return user;
	}

	public ArrayList<UserDto> getAllUser() {

		ArrayList<UserDto> listUser = new ArrayList<>();

		Connection connection = DataProvider.getConnection();
		String sql = "SELECT * FROM USER";

		Statement statement;
		try {
			statement = connection.createStatement();

			ResultSet resultSet = statement.executeQuery(sql);
			while (resultSet.next()) {
				UserDto user = new UserDto();
				user.setUserId(resultSet.getInt("user_id"));
				user.setName(resultSet.getString("name"));
				user.setEmailAddress(resultSet.getString("email_address"));
				try {
					user.setDob(resultSet.getTimestamp("dob"));
				} catch (Exception e) {
					user.setRole(null);
				}
				user.setRole(resultSet.getString("role"));
				listUser.add(user);
			}

			statement.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return listUser;
	}

}
